---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
Retrieves a list of Documents under a particular folder by the folder's HierarchyKey. Security IS enforced.

Returns:
Everything from the DocumentMain table except the blob. Also includes DocumentTypeName, DocumentTypeDesc,
and DocumentIconURL from DocumentTypeRef and HierarchyKey from the Hierarchy element representing the document.
*/
CREATE PROC [dbo].[asi_DocumentMainListByParentHierarchyKey]
   @parentHierarchyKey uniqueidentifier,
   @userKey uniqueidentifier,
   @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
   @publishedOnly bit = 0,
   @ignoreLicensing bit = 0
AS
BEGIN
   DECLARE @t1 TABLE(
          HierarchyKey uniqueidentifier,
          DocumentKey uniqueidentifier,
          DocumentVersionKey uniqueidentifier,
          DocumentStatusCode int,
          DocumentName nvarchar(100),
          AlternateName nvarchar(100),
          DocumentDescription nvarchar(250),
          DocumentTypeCode nvarchar(3),
          IsSystem bit,
          ContainsChildrenFlag bit,
          RelatedDocumentVersionKey uniqueidentifier,
          AccessKey uniqueidentifier,
          DefaultChildAccessKey uniqueidentifier,
          StatusUpdatedOn datetime,
          StatusUpdatedByUserKey uniqueidentifier,
          CreatedOn datetime,
          CreatedByUserKey uniqueidentifier,
          UpdatedOn datetime,
          UpdatedByUserKey uniqueidentifier,
          DocumentTypeName nvarchar(255),
          DocumentTypeDesc nvarchar(100),
          DocumentIconURL nvarchar(256),
          RelatedDocumentKey uniqueidentifier,
          RelatedDocumentTypeCode nvarchar(3),
          RelatedDocumentName nvarchar(100),
          RelatedAlternateName nvarchar(100),
          RelatedDocumentDescription nvarchar(250),
          RelatedDocumentTypeName nvarchar(255),
          RelatedDocumentTypeDesc nvarchar(100),
          RelatedDocumentIconURL nvarchar(256),
          RelatedShortcutIconURL nvarchar(256),
          RelatedDocumentStatusCode int,
          SortOrder int
   )
   
   INSERT INTO @t1
   SELECT a.HierarchyKey,
          b.DocumentKey,
          b.DocumentVersionKey,
          b.DocumentStatusCode,
          b.DocumentName,
          b.AlternateName,
          b.DocumentDescription,
          b.DocumentTypeCode,
          b.IsSystem,
          b.ContainsChildrenFlag,
          b.RelatedDocumentVersionKey,
          b.AccessKey,
          b.DefaultChildAccessKey,
          b.StatusUpdatedOn,
          b.StatusUpdatedByUserKey,
          b.CreatedOn,
          b.CreatedByUserKey,
          b.UpdatedOn,
          b.UpdatedByUserKey,
          c.DocumentTypeName,
          c.DocumentTypeDesc,
          c.DocumentIconURL,
          d.DocumentKey AS RelatedDocumentKey,
          d.DocumentTypeCode AS RelatedDocumentTypeCode,
          d.DocumentName AS RelatedDocumentName,
          d.AlternateName AS RelatedAlternateName,
          d.DocumentDescription AS RelatedDocumentDescription,
          e.DocumentTypeName AS RelatedDocumentTypeName,
          e.DocumentTypeDesc AS RelatedDocumentTypeDesc,
          e.DocumentIconURL AS RelatedDocumentIconURL,
          e.ShortcutIconURL AS RelatedShortcutIconURL,
          d.DocumentStatusCode AS RelatedDocumentStatusCode,
          a.SortOrder
     FROM [dbo].[Hierarchy] a INNER JOIN [dbo].[DocumentMain] b ON a.UniformKey = b.DocumentVersionKey
      INNER JOIN [dbo].[DocumentTypeRef] c ON b.DocumentTypeCode = c.DocumentTypeCode
          LEFT OUTER JOIN [dbo].[DocumentMain] d ON b.RelatedDocumentVersionKey = d.DocumentVersionKey
          LEFT OUTER JOIN [dbo].[DocumentTypeRef] e ON d.DocumentTypeCode = e.DocumentTypeCode
    WHERE a.ParentHierarchyKey = @parentHierarchyKey
    
    SELECT  
          HierarchyKey,
          DocumentKey,
          DocumentVersionKey,
          DocumentStatusCode,
          DocumentName,
          AlternateName,
          DocumentDescription,
          DocumentTypeCode,
          IsSystem,
          ContainsChildrenFlag,
          RelatedDocumentVersionKey,
          AccessKey,
          DefaultChildAccessKey,
          StatusUpdatedOn,
          StatusUpdatedByUserKey,
          CreatedOn,
          CreatedByUserKey,
          UpdatedOn,
          UpdatedByUserKey,
          DocumentTypeName,
          DocumentTypeDesc,
          DocumentIconURL,
          RelatedDocumentKey,
          RelatedDocumentTypeCode,
          RelatedDocumentName,
          RelatedAlternateName,
          RelatedDocumentDescription,
          RelatedDocumentTypeName,
          RelatedDocumentTypeDesc,
          RelatedDocumentIconURL,
          RelatedShortcutIconURL
     FROM @t1 t
    WHERE ((t.DocumentStatusCode IN (10,20,30,70) AND @publishedOnly = 0)
       OR (t.DocumentStatusCode IN (40,60)
      AND (@publishedOnly = 1
       OR NOT EXISTS (
          SELECT 1
            FROM [dbo].[DocumentMain] f
           WHERE f.DocumentVersionKey = t.DocumentVersionKey
             AND f.DocumentStatusCode IN (10,20,30,70)))))
      AND ((t.RelatedDocumentStatusCode IN (10,20,30,70) AND @publishedOnly = 0)
       OR t.RelatedDocumentStatusCode IS NULL
       OR (t.RelatedDocumentStatusCode IN (40,60)
      AND (@publishedOnly = 1
       OR NOT EXISTS (
          SELECT 1
            FROM [dbo].[DocumentMain] g
           WHERE g.DocumentVersionKey = t.RelatedDocumentVersionKey
             AND g.DocumentStatusCode IN (10,20,30,70)))))
      AND EXISTS(
          SELECT 1
            FROM [dbo].[AccessItem] INNER JOIN [dbo].[UserToken] ON AccessItem.Grantee = UserToken.Grantee OR AccessItem.Grantee = @loggedInUserGroupKey
           WHERE AccessItem.AccessKey = t.AccessKey
             AND UserToken.UserKey=@userKey
             AND (AccessItem.Permission&3)>0)
      AND (@ignoreLicensing = 1
       OR NOT EXISTS (SELECT 1 FROM UniformLicense WHERE UniformKey = DocumentVersionKey)
         OR EXISTS(
          SELECT 1
            FROM [dbo].[UniformLicense] INNER JOIN [dbo].[LicenseLegacyList] l ON UniformLicense.LicenseKey = l.LicenseLegacyKey
           WHERE UniformLicense.UniformKey = DocumentVersionKey))
    ORDER BY SortOrder, DocumentName
END


GO